MySQL常用SQL语句
一、数据库操作
1．创建数据库：
Mysql> CREATE DATABASE databaseName;
Mysql数据库的数据文件默认存放在/usr/local/mysql/var/目录下，每个数据库对应一个子目录，用于存储数据表文件；
新建的空数据库在/usr/local/mysql/var/目录下会自动生成一个与新建的库名相同的空文件夹。
例：mysql> create database testdb;
mysql> create database if not exists testdb;
mysql> create schema if not exists student characterset 'gbk' collate 'gbk_chinese_ci';
2．删除数据库
Mysql>DROP DATABASE databseName;
例： mysql> drop database testdb;
3．更改表名
命令：alter table 原表名rename to 新表名;
mysql>alter table tb1 rename to tb11;数据库进行改名。
4．改变数据表引擎
mysql>alter table tb1 engine="MyISAM";
5．坏库扫描修复
cd /var/lib/mysql/xxx && myisamchk playlist_block
二、数据表操作
1．创建数据表
Mysql>CREATE TABLE [if not exists] tableName (字段1名称 类型，字段2名称 类型，… ，PRIMARY kEY (主键名))
mysql> create table table_name(column_name datatype {identity |null|not null},f_time TIMESTAMP(8),…) ENGINE=MyISAM AUTO_INCREMENT=3811 DEFAULT CHARSET=utf8;
例：
mysql> create table tb (id int unsigned not null auto_increment primary key, Name char(20) not null, Age tinyint not null);
mysql> create table tb (id int unsigned not null auto_increment, Name char(20) not null, Age tinyint not null, primary key(id));
mysql> create database mydb;
mysql> use mydb;
mysql> create table students(name char(20) not null, age tinyint unsigned, gender char(1) not null);
mysql> create table courses(ID tinyint unsigned not null auto_increment primary key, Couse varchar(50) not null);
mysql> create table courses(name char(20) not null, age tinyint unsigned, gender char(1) not null);
复制数据表：从一张表中查出需要的数据并创建为一个新表：
create [temporary] table 新表名 select * from 旧表名;
例： mysql> create table testcourses select * from courses where CID <=2;
如果很多字段的属性没有存在，则需要自己重新定义：select组合成需要的语句。
例：create table teststu(id int not null auto_increment, primary key(id) ) select name, age from students;
复制数据表：以其它表为模板，创建一个新表,字段的属性还会存在 ，复制数据库结构，创建一个结构完全相同的表：
create [temporary] table [if not exists] 表名like 已存在的表名;
例： mysql> create table test like courses;
mysql> SHOW CREATE TABLE tableName #显示创建tableName表的语句
2．删除表
Mysql>DROP TABLE databaseName.tableName;
等同于：
Mysql>user databaseName;
Mysql>DROP TABLE tableName;
mysql> drop table testcourses;
3．修改表
ALTER TABLE tableName;
增加字段，例：
mysql>alter table students add course varchar(100);
mysql> alter table tb1 add sex enum('M','F') default 'M' not null after id;#新增sex字段为enum类型，放在id的后面.
修改字段属性：
mysql>alter table tb1 modify name char(30) not null; #改变vip的username为char(30);
改变字段，并设置在最前面字段：如：
mysql>alter table tb1 modify name mediumtext first; #改变name,并将它设在最前面字段，还有一个是after 字段，是指放在某字段后，
修改字段，并移动字段到第一个字段后：
mysql>alter table students change course Course varchar(100) after name;
修改表结构中的数据类型，例：
mysql>alter table courses modify id smallint default 1; #更改数据记录为1. 或default NULL;
设置自动增长列，例：
mysql> create table tb1 (id int unsigned not null, Name char(20) not null, Age tinyint not null);
mysql>#alter table tb1 modify id smallint auto_increment;#执行一下这句会提示错误，因为mysql中要求自动增长的列设为主键
mysql>alter table tb1 add primary key (id);#设主键id,执行这条语句后再执行上面语句就没问题了。
对已有字段改名，如：
mysql>alter table tb1 change sex usersex tinyint default 0 not null after Age; #改名字段sex为usersex并改变类型和位置。
注：仅alter中modify, change很相似，但是modify不能改名只能改结构，但change即可以改名，也可以改类型。
删除字段
mysql>alter table tb1 drop usersex;#删除 usersex字段，警告，所有的该字段数据都会丢失。
注意，修改数据结构是一个很危险的事，最好做好备份，以防不侧。
小结：
• 查看列：desc 表名;
• 修改表名：alter table t_book rename to bbb;
• 添加列：alter table 表名 add column 列名 varchar(30);
• 删除列：alter table 表名 drop column 列名;
• 修改列名MySQL： alter table bbb change nnnnn hh int;
• 修改列名SQLServer：exec sp_rename't_student.name','nn','column';
• 修改列名Oracle：lter table bbb rename column nnnnn to hh int;
• 修改列属性：alter table t_book modify name varchar(22);
4．创建索引
MYSQL常用的索引类型主要有以下几种：普通索引、唯一索引、主键索引、组合索引。
可以在建表的时候加入index indexname (列名)创建索引，也可以手工用命令生成：
1）CREATE INDEX可对表增加普通索引或UNIQUE索引。
CREATE INDEX index_name ON table_name (col_name[(length)],… )
如果索引字段是CHAR，varchar类型，length可以指定小于字段实际长度；如果是BLOB和TEXT类型，必须指定length。
在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值、但允许有空值。
CREATE UNIQUE INDEX index_name ON table_name (column_name)
例：mysql> CREATE INDEX age_index ON tb (Age(10));
如果您希望以降序索引某个列中的值，您可以在列名称之后添加保留字 DESC：
CREATE INDEX age_index ON tb (Age DESC)
组合索引：假如您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：
CREATE INDEX idx_name ON table_name(table_col_1,table_col_2,...,table_col_n);
它允许使用多个列作为索引列。
例： CREATE INDEX age_index ON tb (Age, name)
2）ALTER TABLE也可用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
注：在创建索引时，可以规定索引能否包含重复值。如果不包含，则索引应该创建为PRIMARY KEY或UNIQUE索引。对于单列惟一性索引，这保证单列不包含重复的值。对于多列惟一性索引，保证多个值的组合不重复。
PRIMARY KEY索引和UNIQUE索引非常类似。事实上，PRIMARY KEY索引仅是一个具有名称PRIMARY的UNIQUE索引，即一种特殊的唯一索引，且不允许有空值。这表示一个表只能包含一个PRIMARY KEY，因为一个表中不可能具有两个同名的索引，即一个表只能有一个主键索引。
5．显示现有索引
SHOW INDEX FROM tbname [FROM db_name] #显示现有索引
SHOW KEYS FROM tbname
· Table 表的名称。
· Non_unique 如果索引不能包括重复词，则为0。如果可以，则为1。
· Key_name 索引的名称。
· Seq_in_index 索引中的列序列号，从1开始。
· Column_name 列名称。
· Collation 列以什么方式存储在索引中。在MySQL中，有值‘A’(升序)或NULL(无分类)。
· Cardinality 索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
· Sub_part 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
· Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL。
· Null 如果列含有NULL，则含有YES。如果没有，则该列含有NO。
· Index_type 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
· Comment 更多评注。
例： mysql>SHOW INDEX FROM tb;
mysql> repair TABLE date QUICK; #索引列相关变量变化后自动重建索引
6．删除索引
可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
其中，前两条语句是等价的，删除掉table_name中的索引index_name。
第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。
如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。
使用索引的注意事项
1)、索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以在数据库设计时尽量不要让字段的默认值为NULL。
2)、使用短索引
对列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3)、不要在列上进行运算
在列上进行运算，将导致索引失效而进行全表扫描。
4)、不使用NOT和<>操作
5)、索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
7．约束条件
可以在创建表的时候同时创建约束，如：
create table person
(
id smallint not null auto_increment,
name varchar(10) not null,
company_id smallint,
//主键约束
primary key(id),
//唯一约束,也可以指定约束的名称： unique key constraint_name (name),
unique key(name),
//外键约束,也可以指定约束的名称：constraint constraint_name foreign key(company_id) references company(company_id)
foreign key(company_id) references company(company_id)
)
也可创建表以后，再创建约束
create table person
(
id smallint not null,
name varchar(10) not null,
company_id smallint
)
//创建主键约束
alter table person add primary key(id);
或alter table person add constraint primary_k primary key(id);
//再将主键列设为auto_increment:
alter table person modify id smallint auto_increment;
//删除主键约束
alter table person drop primary key;
删除主键约束的时候，如果主键已经是auto_increment型，则无法删除，因为只有键才能是auto_increment型的
外键关系(Foreign Key relationships)讨论的是父表(categories)与子表(articles)的关系，通过引入外键(Foreign Key)这个概念来保证参照完整性(Referential integrity)。
//创建外键约束
alter table person add foreign key(company_id) references company(company_id);
alter table person add constraint foreing_k foreign key(company_id) references company(company_id);
创建外键约束以后，系统自动为外键列创建了一个key，用下面的方式删除外键约束后，这个key仍然存在。
如果不能在“被reference的表”里找到包含“被reference字段”的索引，或者是两个关联字段类型不匹配，则会出现ERROR 1005 (HY000): Can't create table … (errno: 150)错误
//删除外键约束
alter table person drop foreign key foreign_k;
//创建唯一约束
alter table person add unique key(name);
alter table person add constraint unique_k unique key(name);
//删除唯一约束
因为创建unique约束后，系统会自动给此列创建索引
alter table person drop index name;
约束名称可以用 show create table tablename 命令查看
小结：
添加主键约束：alter table 表名 add constraint 主键 （形如：PK_表名） primary key 表名(主键字段);
添加外键约束：alter table 从表 add constraint 外键（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
删除主键约束：alter table 表名 drop primary key;
删除外键约束：alter table 表名 drop foreign key 外键（区分大小写）;
查看约束，可以从information_schema架构下的系统表查看：
SELECT * FROM information_schema.`TABLE_CONSTRAINTS`;
查看触发器
SELECT * FROM information_schema.`TRIGGERS`;
三．数据操作
1．插入数据： INSERT INTO 表名（字段1，字段2，…） VALUES（字段1的值，字段2的值，…）
insert into tb_name （col，col2，....) values (val1,val2,....）;
insert into tutors （Tname，Gender，Age） values ('jerry','M',24); -----批量插入方式
insert into tutors set Tname='Tom',Genser='F',Age=30; -----只能实现单个字段插入
insert into tutors （Tname,Gender,Age) selectName,Genser,Age from students where Age >=20
2．修改数据记录：UPDATE表名SET字段名1=字段值1[，字段名2=字段值2]WHERE 条件表达式
mysql>update students set Course='wg'whereName='j'; -----更改j的课程为wg
3．删除数据：mysql> DELETE FROM 表名 WHERE 条件表达式
mysql>deletefrom students where Course='wg';
4．查询：
select * from tutors orderby TID desc limit 1; -----查看降序的第一行
select last_insert_ID（）; -----查询插入的最后一个序列号
* 查询及常用函数
mysql> select t1.name, t2.salary from employee AS t1, info AS t2 where t1.name = t2.name;
mysql> select college, region, seed from tournament ORDER BY region, seed;
mysql> select col_name from tbl_name WHERE col_name > 0;
mysql> select DISTINCT …… [DISTINCT关键字可以除去重复的记录]
mysql> select DATE_FORMAT(NOW(),’%m/%d/%Y’) as DATE, DATE_FORMAT(NOW(),’%H:%m:%s’) AS TIME;
mysql> select CURDATE(),CURTIME(),YEAR(NOW()),MONTH(NOW()),DAYOFMONTH(NOW()),HOUR(NOW()),MINUTE(NOW());
mysql> select UNIX_TIMESTAMP(),UNIX_TIMESTAMP(20080808),FROM_UNIXTIME(UNIX_TIMESTAMP()); mysql> select PASSWORD(”secret”),MD5(”secret”); #加密密码用
mysql> select count(*) from tab_name order by id [DESC|ASC]; #DESC倒序/ASC正序
* 函数count, AVG, SUM, MIN, MAX, LENGTH字符长度, LTRIM去除开头的空头, RTRIM去尾部空格, TRIM(str)去除首部尾部空格, LETF/RIGHT(str,x)返回字符串str的左边/右边x个字符, SUBSTRING(str,x,y)返回str中的x位置起至位置y的字符
mysql> select BINARY ‘ross’ IN (’Chandler’,’Joey’, ‘Ross’); #BINARY严格检查大小写
* 比较运算符IN, BETWEEN, IS NULL, IS NOT NULL, LIKE, REGEXP/RLIKE
mysql> select count(*),AVG(number_xx),Host,user from mysql.user GROUP by user [DESC|ASC] HAVING user=root; #分组并统计次数/平均值
* 将wp_posts表中post_content字段中文字”old”替换为”new”
mysql> update wp_posts set post_content=replace(post_content,’old’,’new’)
5．表锁定相关
mysql> LOCK TABLE users READ; # 对user表进行只读锁定
mysql> LOCK TABLES user READ, pfolios WRITE #多表锁控制
mysql> UNLOCK TABLES; #不需要指定锁定表名字, MySQL会自动解除所有表锁定
6．事务操作
新表:create TABLE table-name (field-definitions) TYPE=INNODB; --- innodb支持事务
旧表: alter TABLE table-name TYPE=INNODB;
mysql> start transaction #标记一个事务的开始
mysql> insert into….. #数据变更
mysql> ROLLBACK或commit #回滚或提交
mysql> SET AUTOCOMMIT=1; #设置自动提交
mysql> select @@autocommit; #查看当前是否自动提交
7．删除表后，让新增ID自动承接存在的最大ID。
删除自动ID(auto_increment)表下的内容，会出现ID空档。
例如删除ID=6 ID=7
1 2 3 4 5 8....
这样ID就从8开始记录.
这样需要1个方法让他从6来继续
执行sql语句.
ALTER TABLE `表` AUTO_INCREMENT = 6
8．union可以对同一个表的两次查询联合起来。UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。如果表数据量大的话可能会导致用磁盘进行排序。
实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。比如在blog应用中，可以利用一条sql语句实现置顶blog和普通blog的分页显示。
（ SELECT * FROM `blog` WHERE top=1 ORDER BY created DESC )
UNION ( SELECT * FROM `blog` WHERE top = 0 ORDER BY created DESC ) LIMIT 2 , 3
注：union要求联合的两个表所要查找的数据列要一样多，如果一个表中没有另一个表的字段，可以用NULL代替。
UNION ALL只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。从效率上说，UNION ALL 要比UNION快很多
9．SHELL提示符下运行SQL命令
$ mysql -e “show slave status\G ”
见：
